package com.db.drawio.template.db.table.util;


import com.db.drawio.template.db.table.conf.TableColumn;
import com.db.drawio.template.db.table.conf.TableConfig;
import com.db.drawio.template.db.util.DbUtil;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

@Slf4j
public class TableDbUtil {
    /**
     * 获取表格的配置内容
     *
     * @param connection 链接对象
     * @param tableName  数据库表名称
     * @return 表格配置信息
     */
    public static TableConfig getTableConfig(Connection connection, String tableName) {
        TableConfig tableConfig = new TableConfig();
        tableConfig.setTableName(tableName);
        try {
            // 获取数据库表PK的迭代器
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            Set<TableColumn> primaryKeys = getPrimaryKeys(databaseMetaData, tableName);
            Set<TableColumn> columns = getColumns(databaseMetaData, tableName);
            Set<TableColumn> importedKeys = getImportedKeys(databaseMetaData, tableName);
            // 除去主键和外键的key
            Set<TableColumn> updatedColumns = new HashSet<>(columns);
            for (TableColumn column : updatedColumns) {
                for (TableColumn primaryKeyColumn : primaryKeys) {
                    if (column.getColumnName().equals(primaryKeyColumn.getColumnName())) {
                        column.setColumnAttr("PK");
                    }
                }
                for (TableColumn importedColumn : importedKeys) {
                    if (column.getColumnName().equals(importedColumn.getColumnName())) {
                        column.setColumnAttr("FK");
                    }
                }
                if (column.getColumnName().isEmpty()) {
                    column.setColumnAttr("");
                }
            }
            // 遍历加入到TableConfig中
            List<TableColumn> tableColumns = new ArrayList<>();
            // 按照键码的重要性排序加入
            // 加入pk
            for (TableColumn column : updatedColumns) {
                for (TableColumn primaryKeyColumn : primaryKeys) {
                    if (column.getColumnName().equals(primaryKeyColumn.getColumnName())) {
                        tableColumns.add(column);
                    }
                }
            }
            // 加入fk
            for (TableColumn column : importedKeys) {
                for (TableColumn importedKey : primaryKeys) {
                    if (column.getColumnName().equals(importedKey.getColumnName())) {
                        tableColumns.add(column);
                    }
                }
            }
            // 加入普通列
            for (TableColumn column : updatedColumns) {
                if (!column.getColumnAttr().equals("PK") && !column.getColumnAttr().equals("FK")) {
                    tableColumns.add(column);
                }
            }
            tableConfig.setTableColumns(tableColumns);
            log.debug("获取结束！！！！");
            updatedColumns.forEach(column -> {
                log.debug("tableConfig -- column: {}", column);
            });
        } catch (SQLException e) {
            log.error("获取元数据失败");
            throw new RuntimeException(e);
        }
        return tableConfig;
    }

    /**
     * 获取PK
     *
     * @param databaseMetaData sql元数据
     * @param tableName        表名称
     * @return FK集合
     * @throws SQLException sql错误
     */
    private static Set<TableColumn> getPrimaryKeys(DatabaseMetaData databaseMetaData, String tableName) throws SQLException {
        // 临时用于存放主键的set
        Set<TableColumn> columns = new HashSet<>();
        ResultSet primaryKeysResultSet = databaseMetaData.getPrimaryKeys(null, null, tableName);
        // 获取主键的测试
        while (primaryKeysResultSet.next()) {
            // 列的基本信息
            String columnName = primaryKeysResultSet.getString("COLUMN_NAME");
            /*
            getPrimaryKeys方法返回的ResultSet对象不会包含以下列：
            DATA_TYPE、COLUMN_SIZE、IS_NULLABLE
            //省略
            int typeName = primaryKeysResultSet.getInt("DATA_TYPE");
            int columnSize = primaryKeysResultSet.getInt("COLUMN_SIZE");
            boolean isNullable = "YES".equals(primaryKeysResultSet.getString("IS_NULLABLE"));
             */

            // 构造列对象
            TableColumn column = TableColumn.builder()
                    .columnName(columnName)
                    .columnAttr("PK")
                    .build();
            columns.add(column);
        }

        log.debug("开始展示主键数据");
        columns.forEach(obj -> {
            log.debug("PK: {}", obj);
        });
        return columns;
    }

    /**
     * 获取所有列
     *
     * @param databaseMetaData sql元数据
     * @param tableName        表名称
     * @return FK集合
     * @throws SQLException sql错误
     */
    private static Set<TableColumn> getColumns(DatabaseMetaData databaseMetaData, String tableName) throws SQLException {
        // 获取所有列的测试
        Set<TableColumn> columns = new HashSet<>();
        // 获取列的迭代器对象
        ResultSet columnResultSet = databaseMetaData.getColumns(null, null, tableName, "%");// 选择所有格式的列名称
        // 迭代遍历result
        while (columnResultSet.next()) {
            // 列的基本信息
            String columnName = columnResultSet.getString("COLUMN_NAME");
            int dataTypeInt = columnResultSet.getInt("DATA_TYPE");
            String dataType = DbUtil.getSqlTypeName(dataTypeInt);
            int columnSize = columnResultSet.getInt("COLUMN_SIZE");
            boolean isNullable = "YES".equals(columnResultSet.getString("IS_NULLABLE"));

            // 构造列对象
            TableColumn column = TableColumn.builder()
                    .columnName(columnName)
                    .columnType(dataType)
                    .columnAttr("normal")
                    .columnSize(String.valueOf(columnSize))
                    .isNullable("可空:" + (isNullable ? "是" : "否"))
                    .build();
            columns.add(column);
        }
        log.debug("开始输出所有的列（包含主键）");
        columns.forEach(obj -> {
            log.debug("column: {}", obj);
        });
        return columns;
    }

    /**
     * 外键获取
     *
     * @param databaseMetaData sql元数据
     * @param tableName        表名称
     * @return FK集合
     * @throws SQLException sql错误
     */
    private static Set<TableColumn> getImportedKeys(DatabaseMetaData databaseMetaData, String tableName) throws SQLException {
        Set<TableColumn> columns = new HashSet<>();
        // 获取列的迭代器对象
        ResultSet columnResultSet = databaseMetaData.getImportedKeys(null, null, tableName);// 选择所有格式的列名称
        // 迭代遍历result
        while (columnResultSet.next()) {
            // 列的基本信息
            String columnName = columnResultSet.getString("COLUMN_NAME");
            columnResultSet.getString("DATA_TYPE ");
            String typeName = columnResultSet.getString("DATA_TYPE ");
            int columnSize = columnResultSet.getInt("COLUMN_SIZE");
            boolean isNullable = "YES".equals(columnResultSet.getString("IS_NULLABLE"));

            // 构造列对象
            TableColumn column = TableColumn.builder()
                    .columnName(columnName)
                    .columnType(typeName)
                    .columnAttr("FK")
                    .columnSize(String.valueOf(columnSize))
                    .isNullable("可空:" + (isNullable ? "是" : "否"))
                    .build();
            columns.add(column);
        }
        log.debug("输出外键：");
        columns.forEach(obj -> {
            log.debug("FK: {}", obj);
        });
        return columns;
    }

}
